package br.com.caim.persistencia;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.Connection;

public class ComunicacaoBanco {

	public static ComunicacaoBanco getInstance() {
		return new ComunicacaoBanco();
	}
	
	public String[] realizarAutenticacaoConta(String from, String usuarioId) {
		
		String parLoginSenha[] = { "", "" };
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/caim?user=root&password=root");
			
			Statement stmt = conn.createStatement();
			String sql = "SELECT conta_endereco, conta_senha FROM tb_conta WHERE conta_endereco like '" + from + "' "
			+ "and conta_usuario_id like '" + usuarioId + "'";
			
			// Verifica login e senha do usuario
			ResultSet result = stmt.executeQuery(sql);
			
			// Recupera id do usuario
			parLoginSenha[0] = result.getString("conta_endereco");
			parLoginSenha[1] = result.getString("conta_senha");
			
			System.out.println("TESTE");
			System.out.println(parLoginSenha[0]);
			System.out.println(parLoginSenha[1]);
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return parLoginSenha;
		
	}
	public String verificarAutenticacaoUsuario(String paramLogin, String paramSenha) {
		
		String usuarioId = null;
		Statement stmt = null;
		
		try {
			
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/caim?user=root&password=root");
			
			stmt = conn.createStatement();
			
			String sql = "SELECT * FROM tb_usuario WHERE usuario_login like '" + paramLogin + "' " +
						 									  "and usuario_senha like '" + paramSenha + "'";
			
			// Verifica login e senha do usuario
			ResultSet result = stmt.executeQuery(sql);
			
			while(result.next()) {
			
				// Recupera id do usuario
				usuarioId = result.getString("usuario_id");
				
			}
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return usuarioId;
		
	}
	
	public ArrayList<String> recuperarContasUsuario(String usuarioId) {
		
		ArrayList<String> contas = new ArrayList<String>();
		Statement stmt = null;
		
		try {
		
			Class.forName("com.mysql.jdbc.Driver");
		
			Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/caim?user=root&password=root");
			
			stmt = conn.createStatement();
			
			String sql = "SELECT conta_endereco FROM tb_conta WHERE conta_usuario_id like '" + usuarioId + "'";
			
			ResultSet result = stmt.executeQuery(sql);
			
			while(result.next()) {
				
				contas.add( result.getString("conta_endereco") );
				
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return contas;
		
	}
	
}
